using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.DataAccessProvider;
using DTN.Core;
using DTN.Core.Common;
using System.Web.UI.WebControls;
using System.Web;
using com.dtn.deals.Sercurity;
namespace com.dtn.deals
{
    public class tbl_MemberManager : Itbl_MemberManager
    {
        private static Itbl_MemberManager itbl_MemberManager = null;
        private tbl_MemberManager()
        {
        }
        public static Itbl_MemberManager CreateInstance()
        {
            if (itbl_MemberManager == null)
            {
                itbl_MemberManager = new tbl_MemberManager();
            }
            return itbl_MemberManager;
        }
        public int Inserttbl_Member(tbl_Member tbl_member)
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@fullName", SqlDbType.NVarChar),
																new SqlParameter("@BOD", SqlDbType.NVarChar),
																new SqlParameter("@Email", SqlDbType.NVarChar),
																new SqlParameter("@address", SqlDbType.NVarChar),
																new SqlParameter("@phone", SqlDbType.NVarChar),
																new SqlParameter("@userName", SqlDbType.NVarChar),
																new SqlParameter("@pass", SqlDbType.NVarChar),
																new SqlParameter("@isDel", SqlDbType.TinyInt),
																new SqlParameter("@cDate", SqlDbType.DateTime),
															};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = tbl_member.fullName;
            parameters[2].Value = tbl_member.BOD;
            parameters[3].Value = tbl_member.Email;
            parameters[4].Value = tbl_member.address;
            parameters[5].Value = tbl_member.phone;
            parameters[6].Value = tbl_member.userName;
            parameters[7].Value = tbl_member.pass;
            parameters[8].Value = tbl_member.isDel;
            parameters[9].Value = tbl_member.cDate;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Inserttbl_Member", CommandType.StoredProcedure, parameters);
                tbl_member.ID = Convert.ToInt32(parameters[0].Value);
            }
            catch (Exception ex)
            {
                tbl_member.ID = -1;
            }
            return tbl_member.ID;
        }
        public bool Updatetbl_Member(tbl_Member tbl_member)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@fullName", SqlDbType.NVarChar),
																new SqlParameter("@BOD", SqlDbType.NVarChar),
																new SqlParameter("@Email", SqlDbType.NVarChar),
																new SqlParameter("@address", SqlDbType.NVarChar),
																new SqlParameter("@phone", SqlDbType.NVarChar),
																new SqlParameter("@userName", SqlDbType.NVarChar),
																new SqlParameter("@pass", SqlDbType.NVarChar),
																new SqlParameter("@isDel", SqlDbType.TinyInt),
																new SqlParameter("@cDate", SqlDbType.DateTime),
															};
            parameters[0].Value = tbl_member.ID;
            parameters[1].Value = tbl_member.fullName;
            parameters[2].Value = tbl_member.BOD;
            parameters[3].Value = tbl_member.Email;
            parameters[4].Value = tbl_member.address;
            parameters[5].Value = tbl_member.phone;
            parameters[6].Value = tbl_member.userName;
            parameters[7].Value = tbl_member.pass;
            parameters[8].Value = tbl_member.isDel;
            parameters[9].Value = tbl_member.cDate;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Updatetbl_Member", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }
        public bool UpdateStatus(int id, int isDel)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
                                                                new SqlParameter("@isDel", SqlDbType.Int),
															};
            parameters[0].Value = id;
            parameters[1].Value = isDel;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_UpdateStatustbl_Member", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

        public bool Deletetbl_Member(int id)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Deletetbl_Member", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }
        public tbl_Member Selecttbl_MemberByID(int id)
        {
            IDataReader dr;
            tbl_Member tbl_member = new tbl_Member();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                dr = dataProvider.ExecuteReader("sp_Selecttbl_Member", CommandType.StoredProcedure, parameters);
                while (dr.Read())
                {
                    tbl_member.ID = Convert.ToInt32(dr["ID"].ToString());
                    tbl_member.fullName = (dr["fullName"].ToString());
                    tbl_member.BOD = (dr["BOD"].ToString());
                    tbl_member.Email = (dr["Email"].ToString());
                    tbl_member.address = (dr["address"].ToString());
                    tbl_member.phone = (dr["phone"].ToString());
                    tbl_member.userName = (dr["userName"].ToString());
                    tbl_member.pass = (dr["pass"].ToString());
                    tbl_member.isDel = Convert.ToInt32(dr["isDel"].ToString());
                    tbl_member.cDate = Convert.ToDateTime(dr["cDate"].ToString());
                }

            }
            catch (Exception ex)
            {
                return null;
            }
            return tbl_member;
        }
        public DataTable Selecttbl_MembersDynamic(string Where, string Order)//Where = "username = 'dda' and pass = '\b?_???q????\"\0?''"
        {
           // Where = "username='dda' and pass='12'";
           // Order = "memberid";
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
            parameters[0].Value = Where;
            parameters[1].Value = Order;
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_MembersDynamic", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        public DataTable Selecttbl_MembersWithPages(int StartIndex, int MaxRecords, string where)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = { new SqlParameter("@Startindex",SqlDbType.Int ){Value = StartIndex },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = MaxRecords  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = where  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_MembersWithPagesDynamic", CommandType.StoredProcedure, parrams);
            }
            catch
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        //Cuong Code
        public static tbl_Member GetInfo(string username)
        {
            tbl_Member retVal = null;
            {
                SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
                SqlCommand dbCmd = new SqlCommand("tbl_Member_GetInfoByUserName", dbConn);
                dbCmd.CommandType = CommandType.StoredProcedure;
                dbCmd.Parameters.AddWithValue("@UserName", username);
                try
                {
                    dbConn.Open();
                    SqlDataReader dr = dbCmd.ExecuteReader();
                    if (dr.Read())
                    {
                        retVal = new tbl_Member(dr);
                    }
                }
                finally
                {
                    dbConn.Close();
                }
            }
            return retVal;
        }
        public static void LogInCookie(string username)
        {

            IAuthentication authService = AuthenticationServiceFactory.getLocalInstance();
            AuthenticationRequest authRequest = new AuthenticationRequest(username);
            Ticket ticket = authService.Authenticate(authRequest);
            string encryptedTicket = SecurityMethod.EncryptTicket(ticket);
            HttpCookie cookie = new HttpCookie(Constants.AUTH_COOKIE_NAME, encryptedTicket);
            cookie.Expires = DateTime.Now.AddMinutes(30);
            cookie.Domain = AppEnv.Domain;
            HttpContext.Current.Response.SetCookie(cookie);

        }


        public bool isExistedUsernameToRegister(string username)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@userName", SqlDbType.VarChar){Value = username},
                                                                new SqlParameter("@ReturnVL", SqlDbType.Int){Direction = ParameterDirection.ReturnValue}
															};
            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_IsExistedUsername1", CommandType.StoredProcedure, parameters);
                int kq = int.Parse(parameters[1].Value.ToString());
                if(kq > 0)
                    bresult = true ;


            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }
    }
}
